import sys
import os

# 添加项目根目录到系统路径
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from database import Database

class DormitoryQuery:
    def __init__(self):
        self.db = Database()
    
    def query_all_dormitories(self):
        """
        查询所有宿舍信息
        
        返回:
            list: 宿舍信息列表
        """
        try:
            self.db.connect()
            self.db.cursor.execute(
                "SELECT id, building, room_number, capacity, current_count, status FROM dormitory ORDER BY building, room_number"
            )
            dormitories = self.db.cursor.fetchall()
            return dormitories
        except Exception as e:
            print(f"错误：查询宿舍时发生错误 - {str(e)}")
            return []
        finally:
            self.db.close()
    
    def query_dormitory_by_id(self, dormitory_id):
        """
        根据ID查询宿舍信息
        
        参数:
            dormitory_id (int): 宿舍ID
            
        返回:
            tuple: 宿舍信息
        """
        try:
            self.db.connect()
            self.db.cursor.execute(
                "SELECT id, building, room_number, capacity, current_count, status FROM dormitory WHERE id = ?",
                (dormitory_id,)
            )
            dormitory = self.db.cursor.fetchone()
            return dormitory
        except Exception as e:
            print(f"错误：查询宿舍时发生错误 - {str(e)}")
            return None
        finally:
            self.db.close()
    
    def query_dormitory_by_building_room(self, building, room_number):
        """
        根据楼号和房间号查询宿舍信息
        
        参数:
            building (str): 宿舍楼号
            room_number (str): 房间号
            
        返回:
            tuple: 宿舍信息
        """
        try:
            self.db.connect()
            self.db.cursor.execute(
                "SELECT id, building, room_number, capacity, current_count, status FROM dormitory WHERE building = ? AND room_number = ?",
                (building, room_number)
            )
            dormitory = self.db.cursor.fetchone()
            return dormitory
        except Exception as e:
            print(f"错误：查询宿舍时发生错误 - {str(e)}")
            return None
        finally:
            self.db.close()
    
    def query_available_dormitories(self):
        """
        查询可用宿舍（有空位的宿舍）
        
        返回:
            list: 可用宿舍信息列表
        """
        try:
            self.db.connect()
            self.db.cursor.execute(
                "SELECT id, building, room_number, capacity, current_count, status FROM dormitory WHERE current_count < capacity AND status = 'available' ORDER BY building, room_number"
            )
            dormitories = self.db.cursor.fetchall()
            return dormitories
        except Exception as e:
            print(f"错误：查询可用宿舍时发生错误 - {str(e)}")
            return []
        finally:
            self.db.close()

# 如果直接运行此文件，提供命令行界面
if __name__ == "__main__":
    query = DormitoryQuery()
    
    print("===== 宿舍查询 =====")
    print("1. 查询所有宿舍")
    print("2. 根据楼号和房间号查询宿舍")
    print("3. 查询可用宿舍")
    
    choice = input("请选择查询方式 (1-3): ")
    
    if choice == '1':
        dormitories = query.query_all_dormitories()
        if dormitories:
            print("\n宿舍ID\t楼号\t房间号\t容量\t当前人数\t状态")
            for dorm in dormitories:
                print(f"{dorm[0]}\t{dorm[1]}\t{dorm[2]}\t{dorm[3]}\t{dorm[4]}\t\t{dorm[5]}")
        else:
            print("没有找到任何宿舍信息")
    
    elif choice == '2':
        building = input("请输入宿舍楼号: ")
        room_number = input("请输入房间号: ")
        dormitory = query.query_dormitory_by_building_room(building, room_number)
        
        if dormitory:
            print("\n宿舍ID\t楼号\t房间号\t容量\t当前人数\t状态")
            print(f"{dormitory[0]}\t{dormitory[1]}\t{dormitory[2]}\t{dormitory[3]}\t{dormitory[4]}\t\t{dormitory[5]}")
        else:
            print(f"没有找到宿舍 {building}-{room_number} 的信息")
    
    elif choice == '3':
        dormitories = query.query_available_dormitories()
        if dormitories:
            print("\n宿舍ID\t楼号\t房间号\t容量\t当前人数\t状态")
            for dorm in dormitories:
                print(f"{dorm[0]}\t{dorm[1]}\t{dorm[2]}\t{dorm[3]}\t{dorm[4]}\t\t{dorm[5]}")
        else:
            print("没有找到任何可用宿舍")
    
    else:
        print("无效的选择")